﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using Shrestha.Trigraph.Graph;

namespace Shrestha.Trigraph
{
    public class MySqlDbManager : DbManager
    {
        private MySqlConnection myConn;
        
        public bool InitDb(string host, string database, string user, string password)
        {
            
            TriGraphResources.host = host;
            TriGraphResources.database = database;
            TriGraphResources.user = user;
            TriGraphResources.password = password;

            return InitConnection();
        }

        private bool InitConnection()
        {
            string strProvider = "Data Source=" + TriGraphResources.host + ";Database=" + TriGraphResources.database + ";User ID=" + TriGraphResources.user + ";Password=" + TriGraphResources.password;
            try
            {
                myConn = new MySqlConnection(strProvider);
                myConn.Open();
                return true;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return false;
            }
        }

        public List<TriEvent> GetTriEvents(string queryText)
        {
            
            InitConnection();
            List<TriEvent> teList = new List<TriEvent>();
            MySqlCommand cmd = new MySqlCommand(queryText, myConn);
            MySqlDataReader mdr = cmd.ExecuteReader();
            while (mdr.Read())
            {
                try
                {
                    TriEvent te = new TriEvent(Double.Parse(mdr.GetDouble(0).ToString()), Double.Parse(mdr.GetDouble(1).ToString()), DateTime.Parse(mdr.GetDateTime(2).ToString()));
                    teList.Add(te);
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }
            }
            return teList;
        }

        public List<TriClusterEvent> GetClusteredTriEvents(string queryText)
        {

            InitConnection();
            List<TriClusterEvent> teList = new List<TriClusterEvent>();
            MySqlCommand cmd = new MySqlCommand(queryText, myConn);
            MySqlDataReader mdr = cmd.ExecuteReader();
            while (mdr.Read())
            {
                try
                {
                    TriClusterEvent te = new TriClusterEvent(Double.Parse(mdr.GetDouble(0).ToString()), 
                        Double.Parse(mdr.GetDouble(1).ToString()),
                        Double.Parse(mdr.GetDouble(2).ToString()),
                        Double.Parse(mdr.GetDouble(3).ToString()), 
                        DateTime.Parse(mdr.GetDateTime(4).ToString()),
                        DateTime.Parse(mdr.GetDateTime(5).ToString()),
                        int.Parse(mdr.GetString(6)));
                    teList.Add(te);
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }
            }
            return teList;
        }

        public object ExecuteQuery(string queryText)
        {
            InitConnection();
            MySqlCommand cmd = new MySqlCommand(queryText, myConn);
            return cmd.ExecuteScalar();
        }

        public WorkingSet ExtractParameters(string tableName, string latName, string lngName, string timeName)
        {
            WorkingSet workingSet = new WorkingSet();
            try
            {
                workingSet.MaxDbLat = Double.Parse(ExecuteQuery("select max(" + latName + ") from " + tableName).ToString());
                workingSet.MinDbLat = Double.Parse(ExecuteQuery("select min(if(" + latName + "=0,NULL," + latName + ")) from " + tableName).ToString());

                workingSet.MaxDbLng = Double.Parse(ExecuteQuery("select max(" + lngName + ") from " + tableName).ToString());
                workingSet.MinDbLng = Double.Parse(ExecuteQuery("select min(if(" + lngName + "=0,NULL," + lngName + ")) from " + tableName).ToString());

                workingSet.MaxDbDate = DateTime.Parse(ExecuteQuery("select max(" + timeName + ") from " + tableName).ToString());
                workingSet.MinDbDate = DateTime.Parse(ExecuteQuery("select min(if(" + timeName + "=0,NULL," + timeName + ")) from " + tableName).ToString());

                workingSet.TotalRows = int.Parse(ExecuteQuery("select count(*) from" + tableName).ToString());

                workingSet.TriEventList = GetTriEvents("select " + latName + "," + lngName + "," + timeName + " from " + tableName + " where " + latName + " IS NOT NULL AND " + lngName + " IS NOT NULL AND " + timeName + " IS NOT NULL");
                return workingSet;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return null;
        }

        public WorkingSet ExtractParameters()
        {
            string tableName = TriGraphResources.tableName;
            string latName = TriGraphResources.latName;
            string lngName = TriGraphResources.lngName;
            string timeName = TriGraphResources.timeName;

            WorkingSet workingSet = new WorkingSet();
            workingSet.IsClustered = false;
            try
            {
                workingSet.MaxDbLat = Double.Parse(ExecuteQuery("select max(" + latName + ") from " + tableName).ToString());
                workingSet.MinDbLat = Double.Parse(ExecuteQuery("select min(if(" + latName + "=0,NULL," + latName + ")) from " + tableName).ToString());

                workingSet.MaxDbLng = Double.Parse(ExecuteQuery("select max(" + lngName + ") from " + tableName).ToString());
                workingSet.MinDbLng = Double.Parse(ExecuteQuery("select min(if(" + lngName + "=0,NULL," + lngName + ")) from " + tableName).ToString());

                workingSet.MaxDbDate = DateTime.Parse(ExecuteQuery("select max(" + timeName + ") from " + tableName).ToString());
                workingSet.MinDbDate = DateTime.Parse(ExecuteQuery("select min(if(" + timeName + "=0,NULL," + timeName + ")) from " + tableName).ToString());

                workingSet.TotalRows = int.Parse(ExecuteQuery("select count(*) from" + tableName).ToString());

                workingSet.TriEventList = GetTriEvents("select " + latName + "," + lngName + "," + timeName + " from " + tableName + " where " + latName + " IS NOT NULL AND " + lngName + " IS NOT NULL AND " + timeName + " IS NOT NULL");
                return workingSet;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return null;
        }

        public WorkingSet ExtractParameters(string condition)
        {
            if (null == condition) return ExtractParameters();

            string tableName = TriGraphResources.tableName;
            string latName = TriGraphResources.latName;
            string lngName = TriGraphResources.lngName;
            string timeName = TriGraphResources.timeName;

            WorkingSet workingSet = new WorkingSet();
            try
            {
                workingSet.MaxDbLat = Double.Parse(ExecuteQuery("select max(" + latName + ") from " + tableName + " where " + condition).ToString());
                workingSet.MinDbLat = Double.Parse(ExecuteQuery("select min(if(" + latName + "=0,NULL," + latName + ")) from " + tableName + " where " + condition).ToString());

                workingSet.MaxDbLng = Double.Parse(ExecuteQuery("select max(" + lngName + ") from " + tableName + " where " + condition).ToString());
                workingSet.MinDbLng = Double.Parse(ExecuteQuery("select min(if(" + lngName + "=0,NULL," + lngName + ")) from " + tableName + " where " + condition).ToString());

                workingSet.MaxDbDate = DateTime.Parse(ExecuteQuery("select max(" + timeName + ") from " + tableName + " where " + condition).ToString());
                workingSet.MinDbDate = DateTime.Parse(ExecuteQuery("select min(if(" + timeName + "=0,NULL," + timeName + ")) from " + tableName + " where " + condition).ToString());

                workingSet.TotalRows = int.Parse(ExecuteQuery("select count(*) from " + tableName + " where " + condition).ToString());

                workingSet.TriEventList = GetTriEvents("select " + latName + "," + lngName + "," + timeName + " from " + tableName + " where " + latName + " IS NOT NULL AND " + lngName + " IS NOT NULL AND " + timeName + " IS NOT NULL AND "+condition);
                return workingSet;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return null;
        }

        public bool CheckIfClustersExist()
        {
            InitConnection();
            string query_a = "select count(*) from information_schema.tables where table_name='" + TriGraphResources.tableName + "_cluster'";
            string query_b = "select count(*) from information_schema.tables where table_name='" + TriGraphResources.tableName + "_ac'";
            object a = ExecuteQuery(query_a);
            object b = ExecuteQuery(query_b);
            if(null == a || null == b) return false;
            else if(int.Parse(a.ToString()) == 0 || int.Parse(b.ToString()) == 0) return false;
            else return true;
        }

        public WorkingSet ExtractClusterParameters(int zoomLvl)
        {
            string tableName = TriGraphResources.tableName;
            string latName = TriGraphResources.clusterLatName;
            string lngName = TriGraphResources.clusterLngName;
            string timeName = TriGraphResources.clusterDateName;

            WorkingSet workingSet = new WorkingSet();
            workingSet.IsClustered = true;
            try
            {
                workingSet.MaxDbLat = Double.Parse(ExecuteQuery("select max(max" + latName + ") from " + tableName + "_ac where zoomlvl=" + zoomLvl).ToString());
                workingSet.MinDbLat = Double.Parse(ExecuteQuery("select min(if(min" + latName + "=0,NULL,min" + latName + ")) from " + tableName + "_ac where zoomlvl=" + zoomLvl).ToString());

                workingSet.MaxDbLng = Double.Parse(ExecuteQuery("select max(max" + lngName + ") from " + tableName + "_ac where zoomlvl=" + zoomLvl).ToString());
                workingSet.MinDbLng = Double.Parse(ExecuteQuery("select min(if(min" + lngName + "=0,NULL,min" + lngName + ")) from " + tableName + "_ac where zoomlvl=" + zoomLvl).ToString());

                workingSet.MaxDbDate = DateTime.Parse(ExecuteQuery("select max(max" + timeName + ") from " + tableName + "_ac where zoomlvl=" + zoomLvl).ToString());
                workingSet.MinDbDate = DateTime.Parse(ExecuteQuery("select min(if(min" + timeName + "=0,NULL,min" + timeName + ")) from " + tableName + "_ac where zoomlvl=" + zoomLvl).ToString());

                workingSet.TotalRows = int.Parse(ExecuteQuery("select count(*) from " + tableName + "_ac where zoomlvl= " + zoomLvl).ToString());

                //workingSet.TriEventList = GetTriEvents("select " + latName + "," + lngName + "," + timeName + " from " + tableName + "_cluster where zoomlvl="+zoomLvl);
                workingSet.TriClusterEventList = GetClusteredTriEvents("select maxlat,minlat,maxlng,minlng,maxdate,mindate,cluster from " + tableName + "_ac where zoomlvl=" + zoomLvl);
                return workingSet;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return null;
        }

        public WorkingSet ExtractClusterParameters(double minLat, double maxLat, double minLng, double maxLng, DateTime startDate, DateTime endDate)
        {
            string tableName = TriGraphResources.tableName;
            string latName = TriGraphResources.clusterLatName;
            string lngName = TriGraphResources.clusterLngName;
            string timeName = TriGraphResources.clusterDateName;
            int zoomLvl = GetZoomLvlFrom(minLat, maxLat, minLng, maxLng, startDate, endDate);
            string condition = "minLat >" + minLat + " && maxLat <" + maxLat + " && minLng >" + minLng + " && maxLng<" + maxLng + " && minDate>" + startDate + " && maxDate<" + endDate;
            return ExtractWithCondition(condition, zoomLvl);
        }


        private WorkingSet ExtractWithCondition(string condition, int zoomLvl)
        {
            string tableName = TriGraphResources.tableName;
            string latName = TriGraphResources.clusterLatName;
            string lngName = TriGraphResources.clusterLngName;
            string timeName = TriGraphResources.clusterDateName;
            WorkingSet workingSet = new WorkingSet();
            workingSet.IsClustered = true;
            try
            {
                workingSet.MaxDbLat = Double.Parse(ExecuteQuery("select max(max" + latName + ") from " + tableName + "_ac where zoomlvl=" + zoomLvl + " and " + condition).ToString());
                workingSet.MinDbLat = Double.Parse(ExecuteQuery("select min(if(min" + latName + "=0,NULL,min" + latName + ")) from " + tableName + "_ac where zoomlvl=" + zoomLvl + " and " + condition).ToString());

                workingSet.MaxDbLng = Double.Parse(ExecuteQuery("select max(max" + lngName + ") from " + tableName + "_ac where zoomlvl=" + zoomLvl + " and " + condition).ToString());
                workingSet.MinDbLng = Double.Parse(ExecuteQuery("select min(if(min" + lngName + "=0,NULL,min" + lngName + ")) from " + tableName + "_ac where zoomlvl=" + zoomLvl + " and " + condition).ToString());

                workingSet.MaxDbDate = DateTime.Parse(ExecuteQuery("select max(max" + timeName + ") from " + tableName + "_ac where zoomlvl=" + zoomLvl + " and " + condition).ToString());
                workingSet.MinDbDate = DateTime.Parse(ExecuteQuery("select min(if(min" + timeName + "=0,NULL,min" + timeName + ")) from " + tableName + "_ac where zoomlvl=" + zoomLvl + " and " + condition).ToString());

                workingSet.TotalRows = int.Parse(ExecuteQuery("select count(*) from " + tableName + "_ac where zoomlvl= " + zoomLvl + " and " + condition).ToString());

                //workingSet.TriEventList = GetTriEvents("select " + latName + "," + lngName + "," + timeName + " from " + tableName + "_cluster where zoomlvl="+zoomLvl);
                workingSet.TriClusterEventList = GetClusteredTriEvents("select maxlat,minlat,maxlng,minlng,maxdate,mindate,cluster from " + tableName + "_ac where zoomlvl=" + zoomLvl + " and " + condition);
                return workingSet;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return null;
        }

        private int GetZoomLvlFrom(double minLat, double maxLat, double minLng, double maxLng, DateTime startDate, DateTime endDate)
        {
            string tableName = TriGraphResources.tableName;
            string latName = TriGraphResources.clusterLatName;
            string lngName = TriGraphResources.clusterLngName;
            string timeName = TriGraphResources.clusterDateName;

            return int.Parse(ExecuteQuery("select max(P.zl) from (select count(*) as count, zoomlvl as zl from " + tableName + "_ac where min" + timeName + " > " + startDate + " and max" + timeName + "<" + endDate + " group by zoomlvl) P where P.count > " + TriGraphResources.ClusterSearchTune).ToString());
     
        }

        private int GetZoomLvlFrom(string query, string cquery)
        {
            string tableName = TriGraphResources.tableName;
            string latName = TriGraphResources.clusterLatName;
            string lngName = TriGraphResources.clusterLngName;
            string timeName = TriGraphResources.clusterDateName;

            string queryResult = ExecuteQuery("select count(*) from " + tableName + " where " + query).ToString();
            if (int.Parse(queryResult) < 500)
            {
                return 0;
            }
            else
            {
                queryResult = ExecuteQuery("select max(P.zl) from (select count(*) as count, zoomlvl as zl from " + tableName + "_ac where " + cquery + " group by zoomlvl) P where P.count > " + TriGraphResources.ClusterSearchTune).ToString();
                if (null == queryResult || queryResult == "") return 0;
                else
                    return int.Parse(queryResult);
            }

        }


        public WorkingSet ExtractParameters(string query, string cquery, bool isClustered)
        {
            int zoomLvl = GetZoomLvlFrom(query, cquery);
            if (zoomLvl == 0)
                return ExtractParameters(query);
            else
                return ExtractWithCondition(cquery, zoomLvl);
        }
    }
}
